package com.xpgk.mvc.statistic.turnoverRatioStoreStatistic;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.kit.LogKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.render.Render;
import com.jfinal.render.RenderException;
import com.xpgk.mvc.codeSet.Code;
import com.xpgk.mvc.statistic.daySales.DaySalesStatisticSQL;
import com.xpgk.tools.ToolSqlXml;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class TurnoverRatioStoreStatisticXLS  extends  Render{

	private static final String CONTENT_TYPE = "application/msexcel;charset=UTF-8";

	private final List<Record> data;
	private final String searchCriteria;
	private final String fileName;

	public TurnoverRatioStoreStatisticXLS(List<Record> data,String searchCriteria, String fileName) {
		this.data = data;
		this.searchCriteria = searchCriteria;
		this.fileName = fileName;
	}

	@Override
	public void render() {
		this.response.reset();
		try {
			this.response.setHeader("Content-disposition",
					"attachment; filename*=UTF-8''" + URLEncoder.encode(this.fileName, "UTF-8") + ".xls");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		this.response.setContentType(CONTENT_TYPE);

		OutputStream os = null;
		try {
			os = this.response.getOutputStream();
			WritableWorkbook workbook = Workbook.createWorkbook(os);
			WritableSheet sheet = workbook.createSheet(this.fileName, 0);
			
			
			String  gkflsql=ToolSqlXml.getSqlMy(Code.sqlId_child);
			List<Code>  codeList = Code.dao.find(gkflsql,Code.wcjyy_ids);
			
			
			//主表头
			List<String> main_header = new ArrayList<>();
			main_header.add(fileName);
//			for(int mhj = 0; mhj < codeList.size()+5; mhj ++){
//				main_header.add("");
//				main_header.add("");
//			}
			
			//搜索表头
			List<String> search_header = new ArrayList<>();
			search_header.add("搜索条件：");
			search_header.add(searchCriteria);
//			for(int mhj = 0; mhj < codeList.size()+4; mhj ++){
//				search_header.add("");
//				search_header.add("");
//			}
			
			//第一行表头
			List<String> one_header = new ArrayList<>();
			one_header.add("日期");
			one_header.add("进店人数");
			one_header.add("成交人数");
			one_header.add("未成交人数");
			one_header.add("成交率");
			one_header.add("未成交原因占比");
			for(int i = 0; i < codeList.size()-1; i ++){
				one_header.add("");
			}
			//第二行表头
			List<String> two_header = new ArrayList<>();
			two_header.add("");
			two_header.add("");
			two_header.add("");
			two_header.add("");
			two_header.add("");
			for(int i = 0; i < codeList.size(); i ++){
				two_header.add(codeList.get(i).getStr("name"));
			}
			
			//列名
			List<String> columns = new ArrayList<>();
			columns.add("date");
			columns.add("intonum");
			columns.add("dealnum");
			columns.add("nodealnum");
			columns.add("dealratio");
//			columns.add("dealratio");
			
			for(Code code : codeList){
				columns.add(code.getStr("ids") + "_bl");
			}
			

			int row = 0;//行数

			sheet.setColumnView(0, 15); // 设置列的宽度
			sheet.setColumnView(1, 15); // 设置列的宽度
			sheet.setColumnView(2, 15); // 设置列的宽度
			sheet.setColumnView(3, 15); // 设置列的宽度
			sheet.setColumnView(4, 15); // 设置列的宽度
			sheet.setRowView(0, 600); // 设置行的高度
			sheet.setRowView(1, 600); // 设置行的高度
			sheet.setRowView(2, 600); // 设置行的高度
			sheet.setRowView(3, 600); // 设置行的高度

			//合并单元格
			sheet.mergeCells(0, 0, codeList.size()+4, 0);// 列，行，列，行
			sheet.mergeCells(1, 1, codeList.size()+4, 1);// 列，行，列，行
			sheet.mergeCells(0, 2, 0, 3);// 列，行，列，行
			sheet.mergeCells(1, 2, 1, 3);
			sheet.mergeCells(2, 2, 2, 3);
			sheet.mergeCells(3, 2, 3, 3);
			sheet.mergeCells(4, 2, 4, 3);
			
			sheet.mergeCells(5, 2, codeList.size()+4 , 2);
			
			//主表头样式
			WritableFont wf = new WritableFont(WritableFont.TIMES, 18,  WritableFont.BOLD, true);
			WritableCellFormat wcfF = new WritableCellFormat(wf);
			wcfF.setBackground(Colour.GRAY_25);// 背景色
			wcfF.setAlignment(Alignment.CENTRE);// 水平对齐
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			wcfF.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//搜索表头样式
			WritableCellFormat search_format = new WritableCellFormat();
			search_format.setBackground(Colour.GRAY_25);// 背景色
			search_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			search_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			//表头样式
			WritableCellFormat header_format = new WritableCellFormat();
			header_format.setBackground(Colour.GRAY_25);// 背景色
			header_format.setAlignment(Alignment.CENTRE);// 水平对齐
			header_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			header_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			header_format.setWrap(true);// 是否换行
			//生成主表头
			for (int mi = 0; mi < main_header.size(); mi++) {
				String hea = main_header.get(mi);
				sheet.addCell(new Label(mi, row, hea, wcfF));
			}
			row++;
			//生成搜索表头
			for (int si = 0; si < search_header.size(); si++) {
				String hea = search_header.get(si);
				if(si==0)
				{
					sheet.addCell(new Label(si, row, hea, header_format));
				}else
				{
					sheet.addCell(new Label(si, row, hea, search_format));
				}
				
			}
			row++;
			//生成第一行表头
			for (int i = 0; i < one_header.size(); i++) {
				String header = one_header.get(i);
				sheet.addCell(new Label(i, row, header, header_format));
			}

			row++;
			
			//生成第二行表头
			for (int i = 0; i < two_header.size(); i++) {
				String header = two_header.get(i);
				sheet.addCell(new Label(i, row, header, header_format));
			}

			row++;

			//表格样式
			WritableCellFormat row_format = new WritableCellFormat();
			row_format.setAlignment(Alignment.CENTRE);// 水平对齐
			row_format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直对齐
			row_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 边框
			
			//生成表
			for (int j = 0; j < data.size(); j++) {
				Record record = (Record) data.get(j);

				String[] values = getValues(columns, record);
				
				for (int i = 0; i < values.length; i++) {
						sheet.addCell(new Label(i, row, values[i], row_format));
				}
				row++;
			}

			workbook.write();
			workbook.close();
		} catch (IOException e) {
			throw new RenderException(e);
		} catch (WriteException e) {
			e.printStackTrace();
		} finally {
			try {
				if (os != null) {
					os.flush();
					os.close();
				}
			} catch (IOException e) {
				LogKit.error(e.getMessage(), e);
			}
		}
	}

	private static String[] getValues(List<String> columns, Record record) {
		String[] values = new String[columns.size()];
		int i = -1;
		for (String col : columns) {
			i++;
			Object value = record.get(col);
			if (value == null) {
				continue;
			}
			values[i] = value.toString();
		}
		return values;
	}

}
